﻿Imports System.Windows.Forms
Imports System.ComponentModel
Imports System.Collections.Specialized

Public Class Balance
    Dim dt摸版 As New DataTable

    Private Sub InputUpdate_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim SQL As String = "select ID,TITLE as 名称,Count as 金额,MEMO AS 备注 from CrashStatus "
       
        SQL += " order by 名称"
        Dim dc As New DataGridViewTextBoxColumn

        dc = New DataGridViewTextBoxColumn
        dc.Name = "日期"
        dc.DataPropertyName = "日期"
        dc.Tag = "1"
        dc.Visible = False
        dc.HeaderText = "日期"
        OutlookGrid1.Columns.Insert(0, dc)

        '总合计
        dc = New DataGridViewTextBoxColumn
        dc.Name = "00000000-0000-0000-0000-000000000003"
        dc.DataPropertyName = "00000000-0000-0000-0000-000000000003"
        dc.Tag = "1"
        dc.HeaderText = "总合计"
        OutlookGrid1.Columns.Insert(0, dc)

        '借入
        dc = New DataGridViewTextBoxColumn
        dc.Name = "00000000-0000-0000-0000-000000000002"
        dc.DataPropertyName = "00000000-0000-0000-0000-000000000002"
        dc.Tag = "1"
        dc.HeaderText = "借入"
        OutlookGrid1.Columns.Insert(0, dc)

        '借出
        dc = New DataGridViewTextBoxColumn
        dc.Name = "00000000-0000-0000-0000-000000000001"
        dc.DataPropertyName = "00000000-0000-0000-0000-000000000001"
        dc.Tag = "1"
        dc.HeaderText = "借出"
        OutlookGrid1.Columns.Insert(0, dc)

        Dim dt As DataTable = ct.FillTable(SQL)
        For index As Integer = dt.Rows.Count - 1 To 0 Step -1
            dc = New DataGridViewTextBoxColumn
            dc.Name = dt.Rows(index)("ID").ToString
            dc.DataPropertyName = dt.Rows(index)("ID").ToString
            dc.Tag = "1"
            dc.HeaderText = dt.Rows(index)("名称").ToString
            OutlookGrid1.Columns.Insert(0, dc)
        Next

        Dim dtc As DataColumn

        For index As Integer = 0 To dt.Rows.Count - 1
            dtc = New DataColumn
            dtc.ColumnName = dt.Rows(index)("ID").ToString
            dtc.DataType = GetType(Decimal)
            dtc.DefaultValue = 0
            dtc.ReadOnly = False
            dt摸版.Columns.Add(dtc)
        Next

        dtc = New DataColumn
        dtc.ColumnName = "00000000-0000-0000-0000-000000000001"
        dtc.DataType = GetType(Decimal)
        dtc.DefaultValue = 0
        dtc.ReadOnly = False
        dt摸版.Columns.Add(dtc)

        dtc = New DataColumn
        dtc.ColumnName = "00000000-0000-0000-0000-000000000002"
        dtc.DataType = GetType(Decimal)
        dtc.DefaultValue = 0
        dtc.ReadOnly = False
        dt摸版.Columns.Add(dtc)

        dtc = New DataColumn
        dtc.ColumnName = "00000000-0000-0000-0000-000000000003"
        dtc.DataType = GetType(Decimal)
        dtc.DefaultValue = 0
        dtc.ReadOnly = False
        dt摸版.Columns.Add(dtc)

        dt摸版.Columns.Add("日期")
        Button1_Click(Nothing, Nothing)
      
    End Sub

#Region "查询上一个月按钮"
    Public Sub LoadData1(ByVal itype As Integer)

        Dim strBegin As String = Me.dt开始时间.Value.Year.ToString("0000") + "-" + Me.dt开始时间.Value.Month.ToString("00") + "-" + Me.dt开始时间.Value.Day.ToString("00")
        Dim strEnd As String = Me.dt结束时间.Value.Year.ToString("0000") + "-" + Me.dt结束时间.Value.Month.ToString("00") + "-" + Me.dt结束时间.Value.Day.ToString("00")

        If strBegin > strEnd Then
            ShowMessageBox("开始日期大于结束日期")
            dt开始时间.Focus()
            Return
        End If

        'ct.ExecuteSql("delete from Balance  where date([DATEX]) <> '2011-11-13'")

        Dim sb As New System.Text.StringBuilder
        sb.AppendLine(" SELECT Balance.*  ")
        sb.AppendLine(" FROM Balance ")
        sb.AppendLine(" WHERE date(Balance.DATEX)>='" + strBegin + "'")
        sb.AppendLine(" AND date(Balance.DATEX)<='" + strEnd + "'")

        Dim dtBalance As DataTable = ct.FillTable(sb.ToString)
        Dim newTable As DataTable = dt摸版.Clone
        newTable.TableName = "收入"

        If dtBalance.Rows.Count >= 0 Then

            Dim Year As Integer = Me.dt开始时间.Value.Year
            Dim endmouth As Integer = 0
            Dim strBegin1 As String
            Dim strSql2 As String

            For Year = dt开始时间.Value.Year To dt结束时间.Value.Year
                Dim 开始月 As Integer = 1
                Dim 结束月 As Integer = 12
                If Year = dt开始时间.Value.Year Then
                    开始月 = dt开始时间.Value.Month
                End If
                If Year = dt结束时间.Value.Year Then
                    结束月 = dt结束时间.Value.Month
                End If
                For endmouth = 开始月 To 结束月
                    Dim 开始日 As Integer = 1
                    Dim 结束日 As Integer = DateTime.DaysInMonth(Year, endmouth)
                    If Year = dt开始时间.Value.Year AndAlso endmouth = dt开始时间.Value.Month Then
                        开始日 = dt开始时间.Value.Day
                    End If
                    If Year = dt结束时间.Value.Year AndAlso endmouth = dt结束时间.Value.Month Then
                        结束日 = dt结束时间.Value.Day
                    End If
                    For day As Integer = 开始日 To 结束日
                        strBegin1 = Year.ToString("0000") + "-" + endmouth.ToString("00") + "-" + day.ToString("00")

                        Dim dr As DataRow = newTable.NewRow
                        For index As Integer = 0 To newTable.Columns.Count - 1
                            Select Case newTable.Columns(index).ColumnName
                                Case "更新"
                                    dr("更新") = "更新"
                                Case "生成"
                                    dr("生成") = "生成"
                                Case Else
                                    dr(newTable.Columns(index).ColumnName) = 0
                            End Select
                        Next
                        dr("日期") = strBegin1

                        strSql2 = "select  * from Balance where date([DATEX])='" + strBegin1 + "'"
                        Dim dtData As DataTable = ct.FillTable(strSql2)
                        If dtData.Rows.Count = 0 Then
                            Dim 总额 As Decimal = 0
                            For index As Integer = 0 To newTable.Columns.Count - 2
                                If newTable.Columns(index).ColumnName = "00000000-0000-0000-0000-000000000003" Then
                                    插入数据(newTable.Columns(index).ColumnName, 总额, CDate(strBegin1))
                                    总额 = 0
                                Else
                                    Dim 余额 As Decimal = 得到当前的余额(newTable.Columns(index).ColumnName, "2011-11-13", strBegin1)
                                    插入数据(newTable.Columns(index).ColumnName, 余额, CDate(strBegin1))
                                    总额 += 余额
                                End If

                            Next
                        End If

                        dtData = ct.FillTable(strSql2)
                        For jdex As Integer = 0 To dtData.Rows.Count - 1
                            For kdex As Integer = 0 To newTable.Columns.Count - 1
                                If newTable.Columns(kdex).ColumnName = dtData.Rows(jdex)("CrashStatusID").ToString Then
                                    dr(newTable.Columns(kdex).ColumnName) = dtData.Rows(jdex)("Count")
                                End If
                            Next
                        Next

                        newTable.Rows.Add(dr)
                    Next
                Next
            Next
        Else

            Dim Year As Integer = Me.dt开始时间.Value.Year
            Dim endmouth As Integer = 0
            Dim strBegin1 As String

            For Year = dt开始时间.Value.Year To dt结束时间.Value.Year
                Dim 开始月 As Integer = 1
                Dim 结束月 As Integer = 12
                If Year = dt开始时间.Value.Year Then
                    开始月 = dt开始时间.Value.Month
                End If
                If Year = dt结束时间.Value.Year Then
                    结束月 = dt结束时间.Value.Month
                End If
                For endmouth = 开始月 To 结束月
                    Dim 开始日 As Integer = 1
                    Dim 结束日 As Integer = DateTime.DaysInMonth(Year, endmouth)
                    If Year = dt开始时间.Value.Year AndAlso endmouth = dt开始时间.Value.Month Then
                        开始日 = dt开始时间.Value.Day
                    End If
                    If Year = dt结束时间.Value.Year AndAlso endmouth = dt结束时间.Value.Month Then
                        结束日 = dt结束时间.Value.Day
                    End If
                    For day As Integer = 开始日 To 结束日
                        strBegin1 = Year.ToString("0000") + "-" + endmouth.ToString("00") + "-" + day.ToString("00")

                        Dim dr As DataRow = newTable.NewRow
                        For index As Integer = 0 To newTable.Columns.Count - 1
                            Select Case newTable.Columns(index).ColumnName
                                Case "更新"
                                    dr("更新") = "更新"
                                Case "生成"
                                    dr("生成") = "生成"
                                Case Else
                                    dr(newTable.Columns(index).ColumnName) = 0
                            End Select
                        Next
                        dr("日期") = strBegin1
                        newTable.Rows.Add(dr)
                    Next
                Next
            Next

        End If

        Dim dv As DataView = newTable.DefaultView
        dv.Sort = "日期 desc"
        newTable = dv.ToTable

        Me.OutlookGrid1.AutoGenerateColumns = False
        Me.OutlookGrid1.BindData(newTable, "收入")

        For index As Integer = 0 To OutlookGrid1.Rows.Count - 1
            OutlookGrid1.Rows(index).HeaderCell.Value = newTable.Rows(index)("日期")
        Next

        'Me.OutlookGrid2.Rows.Clear()
        'Dim strBegin2 As String = Now.Year.ToString("0000") + "-" + Now.Month.ToString("00") + "-" + Now.Day.ToString("00")
        '插入一行(strBegin2, newTable)

    End Sub

    Private Sub 插入一行(ByVal strBegin2 As String, ByVal newTable As DataTable)
        'Me.OutlookGrid2.Rows.Clear()
        ''Dim strBegin2 As String = Now.Year.ToString("0000") + "-" + Now.Month.ToString("00") + "-" + Now.Day.ToString("00")
        'Dim 借出没有还的钱 As Decimal = 得到当前借出没有还的钱("", "2009-11-13", strBegin2)
        'Dim 借入没有还的钱 As Decimal = 得到当前借入出没有还的钱("", "2009-11-13", strBegin2)
        'Dim 总额 As Decimal = 0
        'For index As Integer = 0 To newTable.Columns.Count - 2
        '    总额 += CDec(newTable.Rows(0)(index))
        'Next
        '总额 += 借出没有还的钱
        '总额 -= 借入没有还的钱

        'Me.OutlookGrid2.Rows.Add(New Object() {借出没有还的钱, 借入没有还的钱, 总额})
    End Sub
    Private Function 得到当前借出没有还的钱(ByVal CrashId As String, ByVal strBegin As String, ByVal strEnd As String) As Decimal

        Dim dtDataTable As New DataTable
        dtDataTable.Columns.Add("CrashStatusCount1", GetType(Decimal))

        For Type As Integer = 0 To 6
            Select Case Type

                Case 3
                    '借给别人钱
                    Dim sb As New System.Text.StringBuilder
                    sb.AppendLine(" SELECT ifnull(sum(CrashInOut.CrashStatusCount),0.0) as CrashStatusCount1  ")
                    sb.AppendLine(" FROM CrashInOut ")

                    sb.AppendLine(" inner join WysBorrowLend WysBorrowLend on WysBorrowLend.ID=CrashInOut.InOutID ")
                    sb.AppendLine(" and WysBorrowLend.status = 0 ")

                    sb.AppendLine(" WHERE date(CrashInOut.DATETIME)>'" + strBegin + "'")
                    sb.AppendLine(" AND date(CrashInOut.DATETIME)<='" + strEnd + "'")
                    sb.AppendLine(" AND CrashInOut.type = 3")

                    Dim dtDataTable1 As DataTable = ct.FillTable(sb.ToString)
                    dtDataTable.Rows.Add(New Object() {dtDataTable1.Rows(0)(0)})

                Case 4
                    '别人还钱
                    Dim sb As New System.Text.StringBuilder
                    sb.AppendLine("select ifnull(sum(WysBorrowLend.Count),0.0)*-1 CrashStatusCount1")
                    sb.AppendLine("from CrashInOut INNER JOIN WysBorrowLend")
                    sb.AppendLine("ON CrashInOut.LendId=WysBorrowLend.ID")
                    sb.AppendLine("INNER JOIN WysBorrowLend WysBorrowLend1")
                    sb.AppendLine("ON WysBorrowLend1.ID=WysBorrowLend.LendBorrowUserName")
                    sb.AppendLine("AND WysBorrowLend1.Status=0")

                    sb.AppendLine(" WHERE date(WysBorrowLend.DATETIME)>'" + strBegin + "'")
                    sb.AppendLine(" AND date(WysBorrowLend.DATETIME)<='" + strEnd + "'")
                    sb.AppendLine(" AND CrashInOut.type = 4")

                    Dim dtDataTable1 As DataTable = ct.FillTable(sb.ToString)
                    dtDataTable.Rows.Add(New Object() {dtDataTable1.Rows(0)(0)})

                Case 5
                    ''冲帐
                Case 6
            End Select
        Next

        Return CDec(dtDataTable.Compute("sum(CrashStatusCount1)", ""))

    End Function

    Private Function 得到当前借入出没有还的钱(ByVal CrashId As String, ByVal strBegin As String, ByVal strEnd As String) As Decimal

    End Function

    Private Function 得到当前的余额(ByVal CrashId As String, ByVal strBegin As String, ByVal strEnd As String) As Decimal

        Select Case CrashId
            Case "00000000-0000-0000-0000-000000000001"
                Return 得到当前借出没有还的钱("", "2009-11-13", strEnd)
            Case "00000000-0000-0000-0000-000000000002"
                Return 得到当前借入出没有还的钱("", "2009-11-13", strEnd)
            Case "00000000-0000-0000-0000-000000000003"
        End Select
        Dim dtNew As New DataTable
        dtNew.TableName = "收入"

        dtNew.Columns.Add("账户")
        dtNew.Columns.Add("类型")
        dtNew.Columns.Add("金额")
        dtNew.Columns.Add("备注")
        dtNew.Columns.Add("操作时间")
        dtNew.Columns.Add("操作用户")
        dtNew.Columns.Add("类型排序", GetType(Integer))

        Dim dtDataTable As New DataTable
        dtDataTable.Columns.Add("CrashStatusCount1", GetType(Decimal))

        For Type As Integer = 0 To 6
            Select Case Type
                Case 0 '收入
                    Dim sb As New System.Text.StringBuilder
                    sb.AppendLine(" SELECT ifnull(sum(CrashInOut.CrashStatusCount),0.0) as CrashStatusCount1  ")
                    sb.AppendLine(" FROM CrashInOut ")

                    sb.AppendLine(" left join CrashStatus CrashStatus2 on CrashStatus2.ID=CrashInOut.CrashStatusID ")
                    sb.AppendLine(" LEFT JOIN WysInputUser ")
                    sb.AppendLine(" ON CrashInOut.username=WysInputUser.id ")
                    sb.AppendLine(" WHERE date(CrashInOut.DATETIME)>'" + strBegin + "'")
                    sb.AppendLine(" AND date(CrashInOut.DATETIME)<='" + strEnd + "'")
                    sb.AppendLine(" AND CrashInOut.CrashStatusID ='" + CrashId + "'")
                    sb.AppendLine(" AND CrashInOut.type = 0  ")
                    sb.AppendLine(" order by CrashInOut.CrashStatusID,CrashInOut.DATETIME desc")

                    Dim dtDataTable1 As DataTable = ct.FillTable(sb.ToString)
                    dtDataTable.Rows.Add(New Object() {dtDataTable1.Rows(0)(0)})

                    sb.Length = 0
                    sb.AppendLine(" SELECT ifnull(sum(Balance.Count),0.0) as CrashStatusCount1  ")
                    sb.AppendLine(" FROM Balance ")
                    sb.AppendLine(" WHERE date(Balance.DATEX) = '2011-11-13'")
                    sb.AppendLine(" AND Balance.CrashStatusID = '" + CrashId + "'")

                    dtDataTable1 = ct.FillTable(sb.ToString)
                    dtDataTable.Rows.Add(New Object() {dtDataTable1.Rows(0)(0)})
                Case 1 '支出
                    Dim sb As New System.Text.StringBuilder
                    sb.AppendLine(" SELECT ifnull(sum(CrashInOut.CrashStatusCount),0.0)*-1 as CrashStatusCount1  ")
                    sb.AppendLine(" FROM CrashInOut ")

                    sb.AppendLine(" left join CrashStatus CrashStatus2 on CrashStatus2.ID=CrashInOut.CrashStatusID ")
                    sb.AppendLine(" LEFT JOIN WysInputUser ")
                    sb.AppendLine(" ON CrashInOut.username=WysInputUser.id ")
                    sb.AppendLine(" WHERE date(CrashInOut.DATETIME)>'" + strBegin + "'")
                    sb.AppendLine(" AND date(CrashInOut.DATETIME)<='" + strEnd + "'")
                    sb.AppendLine(" AND CrashInOut.CrashStatusID ='" + CrashId + "'")
                    sb.AppendLine(" AND CrashInOut.type = 1  ")
                    sb.AppendLine(" order by CrashInOut.CrashStatusID,CrashInOut.DATETIME desc")

                    Dim dtDataTable1 As DataTable = ct.FillTable(sb.ToString)
                    dtDataTable.Rows.Add(New Object() {dtDataTable1.Rows(0)(0)})
                Case 2 '转帐 
                    '转出
                    Dim sb As New System.Text.StringBuilder
                    sb.AppendLine(" SELECT ifnull(sum(CrashInOut.CrashStatusCount),0.0)*-1 as CrashStatusCount1  ")
                    sb.AppendLine(" FROM CrashInOut ")

                    sb.AppendLine(" left join CrashStatus CrashStatus1 on CrashStatus1.ID=CrashInOut.InOutID ")
                    sb.AppendLine(" LEFT JOIN WysInputUser ")
                    sb.AppendLine(" ON CrashInOut.username=WysInputUser.id ")
                    sb.AppendLine(" WHERE date(CrashInOut.DATETIME)>'" + strBegin + "'")
                    sb.AppendLine(" AND date(CrashInOut.DATETIME)<='" + strEnd + "'")
                    sb.AppendLine(" AND CrashInOut.InOutID ='" + CrashId + "'")
                    sb.AppendLine(" AND CrashInOut.type =2")
                    sb.AppendLine(" order by CrashInOut.CrashStatusID,CrashInOut.DATETIME desc")

                    Dim dtDataTable1 As DataTable = ct.FillTable(sb.ToString)
                    dtDataTable.Rows.Add(New Object() {dtDataTable1.Rows(0)(0)})

                    '转入
                    sb.Length = 0
                    sb.AppendLine(" SELECT ifnull(sum(CrashInOut.CrashStatusCount),0.0)*-1 as CrashStatusCount1  ")
                    sb.AppendLine(" FROM CrashInOut ")

                    sb.AppendLine(" left join CrashStatus CrashStatus2 on CrashStatus2.ID=CrashInOut.CrashStatusID ")
                    sb.AppendLine(" LEFT JOIN WysInputUser ")
                    sb.AppendLine(" ON CrashInOut.username=WysInputUser.id ")
                    sb.AppendLine(" WHERE date(CrashInOut.DATETIME)>'" + strBegin + "'")
                    sb.AppendLine(" AND date(CrashInOut.DATETIME)<='" + strEnd + "'")
                    sb.AppendLine(" AND CrashInOut.CrashStatusID ='" + CrashId + "'")
                    sb.AppendLine(" AND CrashInOut.type =2")
                    sb.AppendLine(" order by CrashInOut.CrashStatusID,CrashInOut.DATETIME desc")

                    dtDataTable1 = ct.FillTable(sb.ToString)
                    dtDataTable.Rows.Add(New Object() {dtDataTable1.Rows(0)(0)})

                Case 3
                    '借给别人钱
                    Dim sb As New System.Text.StringBuilder
                    sb.AppendLine("select ifnull(sum(CrashInOut.CrashStatusCount),0.0)*-1 CrashStatusCount1")
                    sb.AppendLine("from CrashInOut ")
                    sb.AppendLine(" WHERE date(CrashInOut.DATETIME)>'2011-11-14'")
                    sb.AppendLine(" AND date(CrashInOut.DATETIME)<='" + strEnd + "'")
                    sb.AppendLine(" AND CrashInOut.type = 3")
                    sb.AppendLine(" AND CrashInOut.CrashStatusID ='" + CrashId + "'")

                    Dim dtDataTable1 As DataTable = ct.FillTable(sb.ToString)
                    dtDataTable.Rows.Add(New Object() {dtDataTable1.Rows(0)(0)})

                    ''借别人钱
                    'sb.Length = 0
                    'sb.AppendLine(" SELECT ifnull(sum(CrashInOut.CrashStatusCount),0.0) as CrashStatusCount1  ")
                    'sb.AppendLine(" FROM CrashInOut ")

                    'sb.AppendLine(" left join CrashStatus CrashStatus2 on CrashStatus2.ID=CrashInOut.CrashStatusID ")
                    'sb.AppendLine(" LEFT JOIN WysInputUser ")
                    'sb.AppendLine(" ON CrashInOut.username=WysInputUser.id ")
                    'sb.AppendLine(" inner join WysBorrowLend WysBorrowLend on WysBorrowLend.ID=CrashInOut.InOutID ")
                    'sb.AppendLine(" and WysBorrowLend.type=2")
                    'sb.AppendLine(" WHERE date(CrashInOut.DATETIME)>'" + strBegin + "'")
                    'sb.AppendLine(" AND date(CrashInOut.DATETIME)<='" + strEnd + "'")
                    'sb.AppendLine(" AND CrashInOut.CrashStatusID ='" + CrashId + "'")
                    'sb.AppendLine(" AND CrashInOut.type = 3")
                    'sb.AppendLine(" order by CrashInOut.CrashStatusID,CrashInOut.DATETIME desc")

                    'dtDataTable1 = ct.FillTable(sb.ToString)
                    'dtDataTable.Merge(dtDataTable1)
                Case 4
                    '别人还钱
                    Dim sb As New System.Text.StringBuilder
                    sb.AppendLine("select ifnull(sum(CrashInOut.CrashStatusCount),0.0) CrashStatusCount1")
                    sb.AppendLine("from CrashInOut ")
                    sb.AppendLine(" WHERE date(CrashInOut.DATETIME)>'2011-11-13'")
                    sb.AppendLine(" AND date(CrashInOut.DATETIME)<='" + strEnd + "'")
                    sb.AppendLine(" AND CrashInOut.type = 4")
                    sb.AppendLine(" AND CrashInOut.CrashStatusID ='" + CrashId + "'")

                    Dim dtDataTable1 As DataTable = ct.FillTable(sb.ToString)
                    dtDataTable.Rows.Add(New Object() {dtDataTable1.Rows(0)(0)})

                    '    '还给别人钱
                    '    sb.Length = 0
                    '    sb.AppendLine(" SELECT ifnull(sum(CrashInOut.CrashStatusCount),0.0)*-1 as CrashStatusCount1  ")
                    '    sb.AppendLine(" FROM CrashInOut ")

                    '    sb.AppendLine(" left join CrashStatus CrashStatus2 on CrashStatus2.ID=CrashInOut.CrashStatusID ")
                    '    sb.AppendLine(" LEFT JOIN WysInputUser ")
                    '    sb.AppendLine(" ON CrashInOut.username=WysInputUser.id ")
                    '    sb.AppendLine(" inner join WysBorrowLend WysBorrowLend on WysBorrowLend.LendBorrowUserName=CrashInOut.InOutID ")
                    '    sb.AppendLine(" and WysBorrowLend.type= 3 ")
                    '    sb.AppendLine(" WHERE date(CrashInOut.DATETIME)>'" + strBegin + "'")
                    '    sb.AppendLine(" AND date(CrashInOut.DATETIME)<='" + strEnd + "'")
                    '    sb.AppendLine(" AND CrashInOut.CrashStatusID ='" + CrashId + "'")
                    '    sb.AppendLine(" AND CrashInOut.type = 4")
                    '    sb.AppendLine(" order by CrashInOut.CrashStatusID,CrashInOut.DATETIME desc")

                    '    dtDataTable1 = ct.FillTable(sb.ToString)
                    '    dtDataTable.Merge(dtDataTable1)
                Case 5
                    ''冲帐
                Case 6
            End Select
        Next

        Return CDec(dtDataTable.Compute("sum(CrashStatusCount1)", ""))

    End Function

    Private Function 转换对象字符串(ByVal ob As Object) As String
        If ob Is DBNull.Value Then
            Return ""
        End If
        Return ob.ToString
    End Function

    Private Function 转换日期字符串(ByVal ob As Object) As String
        Dim dt As Date = Date.Parse(ob.ToString())
        Return dt.ToString("yyyy年MM月dd日")
    End Function

    Private Function 转换日期字符串月(ByVal ob As Object) As String
        Dim dt As Date = Date.Parse(ob.ToString())
        Return dt.ToString("yyyy年MM月")
    End Function
#End Region

#Region "重载设置窗体函数 A-2010-09-02"

    Public Overrides Function 是否显示设置窗体数据行数() As Boolean
        Return False
    End Function
#End Region

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim strBegin As String = Me.dt开始时间.Value.Year.ToString("0000") + "-" + Me.dt开始时间.Value.Month.ToString("00") + "-" + Me.dt开始时间.Value.Day.ToString("00")
        Dim strEnd As String = Me.dt结束时间.Value.Year.ToString("0000") + "-" + Me.dt结束时间.Value.Month.ToString("00") + "-" + Me.dt结束时间.Value.Day.ToString("00")

        '余额 今天开始
        If dt开始时间.Value < CDate("2011-11-14") Then
            ShowMessageBox("开始日期必须大于2011-11-13")
            dt开始时间.Focus()
            Return
        End If

        If CDate(dt结束时间.Value) > CDate(Now) Then
            ShowMessageBox("结束日期不能大于系统时间")
            dt结束时间.Focus()
            Return
        End If

        If strBegin > strEnd Then
            ShowMessageBox("开始日期大于结束日期")
            dt开始时间.Focus()
            Return
        End If

        If txt支出关联1.Tag.ToString.Trim = "" Then
            ShowMessageBox("必须选择一个帐户")
            btn支出关联1.Focus()
            Return
        End If

        LoadData1(0)
        'OutUpdat_Click(Nothing, Nothing)
        'rad全部折叠_Click(Nothing, Nothing)
    End Sub

    Public Overrides Sub 保存窗体数据到数据库(ByRef dtData As System.Data.DataTable)
        '年
        dtData.Rows.Add(New String() {"开始时间", "", dt开始时间.Value.ToString("yyyy-MM-dd")})
        dtData.Rows.Add(New String() {"结束时间", "", dt结束时间.Value.ToString("yyyy-MM-dd")})

        If Me.rad全部展开.Checked Then
            dtData.Rows.Add(New String() {"全部展开", "", "是"})
        Else
            dtData.Rows.Add(New String() {"全部展开", "", "否"})
        End If

        dtData.Rows.Add(New String() {"帐户ID", "", txt支出关联1.Tag.ToString.Trim})
        dtData.Rows.Add(New String() {"帐户名称", "", txt支出关联1.Text.Trim})

        MyBase.保存窗体数据到数据库(dtData)

    End Sub

    Public Overrides Sub 存取数据库数据到窗体(ByVal dtDbData As System.Data.DataTable)

        If dtDbData.Rows.Count > 0 Then
            '年
            dt开始时间.Value = 查找数据("开始时间", "", Now.ToString("yyyy-MM-dd"), dtDbData)
            dt结束时间.Value = Now.AddDays(-1)

            rad全部展开.Checked = IIf(查找数据("全部展开", "", "是", dtDbData) = "是", True, False)
            Me.rad全部折叠.Checked = IIf(查找数据("全部展开", "", "否", dtDbData) = "否", True, False)

            txt支出关联1.Tag = 查找数据("帐户ID", "", "", dtDbData)
            txt支出关联1.Text = 查找数据("帐户名称", "", "", dtDbData)

        End If
        MyBase.存取数据库数据到窗体(dtDbData)
    End Sub

    Private Sub OutlookGrid1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles OutlookGrid1.CellContentClick

        If e.RowIndex < 0 Then
            Exit Sub
        End If

        Select Case e.ColumnIndex

            Case Me.OutlookGrid1.Columns("更新").Index
                删除数据(CStr(OutlookGrid1.Rows(e.RowIndex).HeaderCell.Value))
                For index As Integer = 0 To OutlookGrid1.Columns.Count - 5
                    插入数据(OutlookGrid1.Columns(index).Name, 金额(OutlookGrid1.Rows(e.RowIndex).Cells(index).Value), CDate(OutlookGrid1.Rows(e.RowIndex).HeaderCell.Value))
                Next
                Button1_Click(Nothing, Nothing)
            Case Me.OutlookGrid1.Columns("生成").Index

            Case Me.OutlookGrid1.Columns("删除").Index
                删除数据(CStr(OutlookGrid1.Rows(e.RowIndex).HeaderCell.Value))
                Button1_Click(Nothing, Nothing)
        End Select

    End Sub

    Private Function 金额(ByVal ob As Object) As Decimal
        If ob Is Nothing Then
            Return 0
        End If
        If ob Is DBNull.Value Then
            Return 0
        End If
        Return CDec(ob)
    End Function

    Private Function 删除数据(ByVal datex As Date) As Boolean

        Dim strSql2 As String = "delete from Balance where date([DATEX])='" + datex + "'"

        ct.ExecuteSql(strSql2)

        Return True

    End Function


    Private Function 插入数据(ByVal crashId As String, ByVal count As Decimal, ByVal datex As Date) As Boolean

        Dim cmd As New ADODB.Command
        Dim sql As String = "Insert into Balance(ID,DATEX,CrashStatusID,Count,OPTIME,USERNAME,Type) Values(?,?,?,?,?,'wangys','1')"
        cmd.CommandText = sql
        cmd.CommandType = ADODB.CommandTypeEnum.adCmdText

        Dim pa As ADODB.Parameter = cmd.CreateParameter(, ADODB.DataTypeEnum.adChar, ADODB.ParameterDirectionEnum.adParamInput, 36, System.Guid.NewGuid.ToString("D"))
        cmd.Parameters.Append(pa)

        pa = cmd.CreateParameter(, ADODB.DataTypeEnum.adDBTimeStamp, ADODB.ParameterDirectionEnum.adParamInput, 100, datex.ToString())
        cmd.Parameters.Append(pa)

        pa = cmd.CreateParameter(, ADODB.DataTypeEnum.adChar, ADODB.ParameterDirectionEnum.adParamInput, 36, crashId)
        cmd.Parameters.Append(pa)

        pa = cmd.CreateParameter(, ADODB.DataTypeEnum.adDecimal, ADODB.ParameterDirectionEnum.adParamInput)
        pa.Value = count
        cmd.Parameters.Append(pa)

        pa = cmd.CreateParameter(, ADODB.DataTypeEnum.adDBTimeStamp, ADODB.ParameterDirectionEnum.adParamInput, 100, DateTime.Now.ToString())
        cmd.Parameters.Append(pa)

        Return ct.ExcCommand(cmd)

    End Function

End Class
